//
//  BGODatabaseConstants.h
//  Bango
//
//  Created by William Shakour on 02/09/2011.
//

#define DATABASE_NAME @"BangoAnalytics.db"
#define DATABASE_VERSION 1

#define DATABASE_TABLENAMEPREFIX @"InApp"

#define DATABASE_DELETETABLES @"DELETE from sqlite_master where type = 'table' AND name LIKE '%@\%'"

#define DATABASE_SELECTPOSTPROFILES @"SELECT _id as profileId, sdkType, sdkVersion, applicationId, applicationType, applicationChannel, applicationVersion, userId, sessionRestartTime, created, connectionType, isRoaming, locationSource, locationAltitude, locationLatitude, locationLongitude, locationAccuracy, operator, operatorName, operatorCountryIso, simOperator, brand, device, model, product, version, buildId, operatorConnectionType FROM [%@] WHERE _id != ?"
#define DATABASE_SELECTPOSTEVENTS @"SELECT _id as eventId, profileId, eventName, eventDetail, eventValue, errorId, eventTime, connectionType, operatorConnectionType, errorLevel FROM [%@] WHERE (eventPosted = 0 OR eventPosted = 1) AND profileId = ?"
#define DATABASE_SELECTPOSTEVENTPARAMETERS @"SELECT _id as eventParameterId, eventId, eventName, eventValue FROM [%@] WHERE eventId = ?"

#define DATABASE_UPDATEPROFILELOCATION @"UPDATE [%@] SET locationLatitude = ?, locationLongitude = ?, locationAltitude = ?, locationAccuracy = ? WHERE _id = ?"
//#define DATABASE_UPDATEPROFILEPOSTED @"UPDATE [%@] SET profilePosted = 2 WHERE _id = ? AND profilePosted = 1"
#define DATABASE_UPDATEPROFILEPOSTED @"UPDATE [%@] SET profilePosted = 2 WHERE _id = ? AND profilePosted = 1 AND (SELECT count(1) from [%@] where eventPosted = 0 and profileid = ?) = 0"
#define DATABASE_UPDATEEVENTPOSTED @"UPDATE [%@] SET eventPosted = 2 WHERE profileId = ? AND eventPosted = 1"

#define DATABASE_UPDATEPROFILEPOSTINPROGRESS @"UPDATE [%@] SET profilePosted = 1 WHERE _id = ?"
#define DATABASE_UPDATEEVENTPOSTINPROGRESS @"UPDATE [%@] SET eventPosted = 1 WHERE profileId = ?"

#define DATABASE_DELETEPROFILE @"DELETE FROM [%@] WHERE profilePosted = 2 AND _id = ?"
#define DATABASE_DELETEEVENT @"DELETE FROM [%@] WHERE eventPosted = 2 AND profileId = ?"
#define DATABASE_DELETEEVENTPARAMETER @"DELETE FROM [%@] WHERE eventId IN (SELECT _id FROM [%@] WHERE eventPosted = 2 AND profileId = ?)"

#define DATABASE_TABLECREATEPROFILE @"CREATE TABLE IF NOT EXISTS [%@] (_id INTEGER PRIMARY KEY, sdkType INTEGER, sdkVersion TEXT, applicationId TEXT, applicationType TEXT, applicationChannel TEXT, applicationVersion TEXT, userId TEXT, sessionRestartTime INTEGER, created DATETIME, connectionType INTEGER, isRoaming BOOLEAN, locationSource TEXT, locationAltitude NUMERIC, locationLatitude NUMERIC, locationLongitude NUMERIC, locationAccuracy NUMERIC, operator TEXT, operatorName TEXT, operatorCountryIso TEXT, simOperator TEXT, brand TEXT, device TEXT, model TEXT, product TEXT, version TEXT, buildId TEXT, operatorConnectionType TEXT, profilePosted BOOLEAN)"
#define DATABASE_TABLECREATEEVENT @"CREATE TABLE IF NOT EXISTS [%@] (_id INTEGER PRIMARY KEY, profileId INTEGER, eventName TEXT, eventDetail TEXT, eventValue TEXT, errorId TEXT, eventTime DATETIME, connectionType TEXT, operatorConnectionType TEXT, errorLevel INTEGER, locationLatitude NUMERIC, locationLongitude NUMERIC, locationAccuracy NUMERIC, eventPosted BOOLEAN)"
#define DATABASE_TABLECREATEEVENTPARAMETER @"CREATE TABLE IF NOT EXISTS [%@] (_id INTEGER PRIMARY KEY, eventId INTEGER, eventName TEXT, eventValue TEXT)"

#define DATABASE_CREATEPROFILE @"INSERT INTO [%@] ('sdkType', 'sdkVersion', 'applicationId', 'applicationType', 'applicationChannel', 'applicationVersion', 'userId', 'sessionRestartTime', 'created', 'connectionType', 'isRoaming', 'locationSource', 'locationAltitude', 'locationLatitude', 'locationLongitude', 'locationAccuracy', 'operator', 'operatorName', 'operatorCountryIso', 'simOperator', 'brand', 'device', 'model', 'product', 'version', 'buildId', 'operatorConnectionType', 'profilePosted') VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
#define DATABASE_CREATEEVENT @"INSERT INTO [%@] ('profileId', 'eventName', 'eventDetail', 'eventValue', 'errorId', 'eventTime', 'connectionType', 'operatorConnectionType', 'errorLevel', 'eventPosted')VALUES(?,?,?,?,?,?,?,?,?,?)"
#define DATABASE_CREATEEVENTPARAMETER @"INSERT INTO [%@] ('eventId', 'eventName', 'eventValue') VALUES (?,?,?)"
#define DATABASE_TABLENAMEPROFILE @"InAppProfiles"
#define DATABASE_TABLENAMEEVENT @"InAppEvents"
#define DATABASE_TABLENAMEEVENTPARAMETER @"InAppEventParameters"

#define DATABASE_COLUMN_PROFILEID @"profileId"
#define DATABASE_COLUMN_SDKTYPE @"sdkType"
#define DATABASE_COLUMN_SDKVERSION @"sdkVersion"
#define DATABASE_COLUMN_APPLICATIONID @"applicationId"
#define DATABASE_COLUMN_APPLICATIONTYPE @"applicationType"
#define DATABASE_COLUMN_APPLICATIONCHANNEL @"applicationChannel"
#define DATABASE_COLUMN_APPLICATIONVERSION @"applicationVersion"
#define DATABASE_COLUMN_USERID @"userId"
#define DATABASE_COLUMN_SESSIONRESTARTTIME @"sessionRestartTime"
#define DATABASE_COLUMN_CREATED @"created"
#define DATABASE_COLUMN_CONNECTIONTYPE @"connectionType"
#define DATABASE_COLUMN_ISROAMING @"isRoaming"
#define DATABASE_COLUMN_LOCATIONSOURCE @"locationSource"
#define DATABASE_COLUMN_LOCATIONALTITUDE @"locationAltitude"
#define DATABASE_COLUMN_LOCATIONLATITUDE @"locationLatitude"
#define DATABASE_COLUMN_LOCATIONLONGITUDE @"locationLongitude"
#define DATABASE_COLUMN_LOCATIONACCURACY @"locationAccuracy"
#define DATABASE_COLUMN_OPERATOR @"operator"
#define DATABASE_COLUMN_OPERATORNAME @"operatorName"
#define DATABASE_COLUMN_OPERATORCOUNTRYISO @"operatorCountryIso"
#define DATABASE_COLUMN_SIMOPERATOR @"simOperator"
#define DATABASE_COLUMN_BRAND @"brand"
#define DATABASE_COLUMN_DEVICE @"device"
#define DATABASE_COLUMN_MODEL @"model"
#define DATABASE_COLUMN_PRODUCT @"product"
#define DATABASE_COLUMN_VERSION @"version"
#define DATABASE_COLUMN_BUILDID @"buildId"
#define DATABASE_COLUMN_OPERATORCONNECTIONTYPE @"operatorConnectionType"
#define DATABASE_COLUMN_EVENTID @"eventId"
#define DATABASE_COLUMN_EVENTNAME @"eventName"
#define DATABASE_COLUMN_EVENTDETAIL @"eventDetail"
#define DATABASE_COLUMN_EVENTVALUE @"eventValue"
#define DATABASE_COLUMN_ERRORID @"errorId"
#define DATABASE_COLUMN_EVENTTIME @"eventTime"
#define DATABASE_COLUMN_ERRORLEVEL @"errorLevel"